<?php

/**
 * This is the model class for table "tbl_user".
 *
 * The followings are the available columns in table 'tbl_user':
 * @property string $id
 * @property string $fullname
 * @property string $email
 * @property string $phone
 * @property string $username
 * @property string $password
 * @property string $role
 * @property integer $status
 * @property string $address
 * @property string $user_created
 * @property string $created_at
 * @property string $user_updated
 * @property string $updated_at
 *
 * The followings are the available model relations:
 * @property Form[] $forms
 * @property Form[] $forms1
 * @property Form[] $forms2
 * @property Log[] $logs
 */
class User extends CActiveRecord {

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'tbl_user';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('fullname, email, password, phone, address', 'required', 'message' => '(*) {attribute} không được trống.'),
            array('email', 'email', 'message' => '(*) Email không đúng định dạng.'),
            array('status', 'numerical', 'integerOnly' => true),
            array('fullname', 'length', 'max' => 100),
            array('email, username, password, role', 'length', 'max' => 50),
            array('phone', 'numerical', 'message'=>'(*) Số điện thoại không đúng.'),
            array('address', 'length', 'max' => 500),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, fullname, email, phone, username, password, role, status, address', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'forms' => array(self::HAS_MANY, 'Form', 'user_create'),
            'forms1' => array(self::HAS_MANY, 'Form', 'user_update'),
            'forms2' => array(self::HAS_MANY, 'Form', 'user_check'),
            'logs' => array(self::HAS_MANY, 'Log', 'user_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'fullname' => 'Họ và tên',
            'email' => 'Email',
            'phone' => 'Số điện thoại',
            'username' => 'Tên tài khoản',
            'password' => 'Mật khẩu',
            'role' => 'Quyền',
            'status' => 'Trạng thái',
            'address' => 'Địa chỉ',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search() {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id, true);
        $criteria->compare('fullname', $this->fullname, true);
        $criteria->compare('email', $this->email, true);
        $criteria->compare('phone', $this->phone, true);
        $criteria->compare('username', $this->username, true);
        $criteria->compare('password', $this->password, true);
        $criteria->compare('role', $this->role, true);
        $criteria->compare('status', $this->status);
        $criteria->compare('address', $this->address, true);

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return User the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    public static function getCurrentUser() {
        if (Yii::app()->user->id !== null && Yii::app()->user->id !== '') {
            $user = User::model()->findByPk(Yii::app()->user->id);
            return $user;
        }
    }
    
    protected function sqlFilterByPayment(){
        $select = ' SELECT u.*,sum(f.remain) as remain';
        $from =' FROM tbl_user u join tbl_form on u.id = f.user_create';
        $where =" where f.remain > 0";
        $group_by =" GROUP BY u.id";
        return $select.$from.$where.$group_by;
    }
    protected function sqlCountFilterByPayment(){
        return "SELECT COUNT(DISTINCT(id)) FROM tbl_user u JOIN tbl_form f ON f.user_create = u.id WHERE f.remain > 0";
    }
    public function getListRemain(){
        $connection = Yii::app()->db;
        $key = "id";
        $sql = $this->sqlFilterByPayment();
        $rowcountsql = $this->sqlCountFilterByPayment();
        $command = $connection->createCommand($rowcountsql);
        $count = $command->queryScalar(); // provide count for pagination
// create data provider that works with CGridView
        $dataProvider = new CSqlDataProvider($sql, array(
            'keyField' => $key,
            'totalItemCount' => $count,
            'pagination' => array(
                'pageSize' => 5,
            ),
        ));
        return $dataProvider;
    }
}
